const Database = require('better-sqlite3')
const path = require('path')
const os = require('os')
const fs = require('fs')
const { v4: uuidv4 } = require('uuid')
const log = require('electron-log')

let db = null

const migrateOldData = async (db) => {
  try {
    // 检查是否存在旧表
    const oldTableExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name='video_tags'
    `).get()

    if (!oldTableExists) return

    // 开始事务
    const migration = db.transaction(() => {
      // 获取所有旧数据
      const oldTags = db.prepare('SELECT * FROM video_tags').all()
      
      for (const oldTag of oldTags) {
        // 插入或获取视频记录
        const videoId = uuidv4()
        db.prepare(
          `INSERT OR IGNORE INTO videos (id, path, name, last_accessed)
           VALUES (?, ?, ?, CURRENT_TIMESTAMP)`
        ).run(videoId, oldTag.video_path, path.basename(oldTag.video_path))
        
        // 获取插入的视频ID
        const video = db.prepare(
          'SELECT id FROM videos WHERE path = ?'
        ).get(oldTag.video_path)

        // 插入或获取标签记录
        const tagId = uuidv4()
        db.prepare(
          `INSERT OR IGNORE INTO tags (id, name, color)
           VALUES (?, ?, ?)`
        ).run(tagId, oldTag.description, oldTag.color || '#f44336')

        // 获取插入的标签ID
        const tag = db.prepare(
          'SELECT id FROM tags WHERE name = ?'
        ).get(oldTag.description)

        // 插入标签段记录
        db.prepare(
          `INSERT INTO video_tag_segments 
           (id, video_id, tag_id, start_time, end_time, description)
           VALUES (?, ?, ?, ?, ?, ?)`
        ).run(
          uuidv4(),
          video.id,
          tag.id,
          oldTag.start_time,
          oldTag.end_time,
          oldTag.description
        )
      }

      // 删除旧表
      db.prepare('DROP TABLE IF EXISTS video_tags').run()
    })

    migration()
  } catch (error) {
    log.error('数据迁移失败:', error)
    throw error
  }
}

export const openDatabase = async () => {
  if (db) return db
  
  try {
    const dbPath = path.join(os.homedir(), '.video-tags.db')
    
    // 确保数据库文件所在目录存在
    const dbDir = path.dirname(dbPath)
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true })
    }
    
    db = new Database(dbPath)

    // 创建数据库表
    db.exec(`
      -- 视频表
      CREATE TABLE IF NOT EXISTS videos (
        id TEXT PRIMARY KEY,
        path TEXT NOT NULL,
        name TEXT NOT NULL,
        duration INTEGER,  -- 视频时长（秒）
        resolution TEXT,   -- 视频分辨率
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_accessed TIMESTAMP,
        UNIQUE(path)
      );

      -- 标签定义表
      CREATE TABLE IF NOT EXISTS tags (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        color TEXT DEFAULT '#f44336',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(name)
      );

      -- 视频标签关联表
      CREATE TABLE IF NOT EXISTS video_tag_segments (
        id TEXT PRIMARY KEY,
        video_id TEXT NOT NULL,
        tag_id TEXT NOT NULL,
        start_time INTEGER NOT NULL,
        end_time INTEGER NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (video_id) REFERENCES videos(id),
        FOREIGN KEY (tag_id) REFERENCES tags(id)
      );

      -- ��加必要的索引
      CREATE INDEX IF NOT EXISTS idx_video_tag_segments_video_id ON video_tag_segments(video_id);
      CREATE INDEX IF NOT EXISTS idx_video_tag_segments_tag_id ON video_tag_segments(tag_id);
      CREATE INDEX IF NOT EXISTS idx_video_tag_segments_times ON video_tag_segments(start_time, end_time);
      CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
      CREATE INDEX IF NOT EXISTS idx_videos_path ON videos(path);
    `)

    // 执行数据迁移
    await migrateOldData(db)

    return db
  } catch (error) {
    log.error('数据库初始化错误:', error)
    throw error
  }
} 